﻿using HotelManager.Model;
using System.Data;

namespace HotelManager.DAL
{
    public class GuestService : IGuestService
    {
        private readonly HotelDbContext _context;

        public GuestService(HotelDbContext context)
        {
            _context = context;
        }

        /// <summary>
        /// 入住登记
        /// </summary>
        /// <param name="guest"></param>
        /// <returns></returns>
        public bool AddGuest(GuestRecord guest)
        {
            //string sql = "insert into GuestRecord(GuestName,Gender,IdentityId,Phone,RoomId,ResideId,ResideDate,Deposit) values(@GuestName,@Gender,@IdentityId,@Phone,@RoomId,@ResideId,@ResideDate,@Deposit);
            //update Room set RoomStateId=@RoomStateId where RoomId=@RoomId";
            var newGuest = new GuestRecord
            {
                GuestName = guest.GuestName,
                Gender = guest.Gender == "男" ? "0" : "1",
                IdentityId = guest.IdentityId,
                Phone = guest.Phone,
                RoomId = guest.RoomId,
                ResideId = guest.ResideId,
                ResideDate = guest.ResideDate,
                Deposit = guest.Deposit
            };
            _context.GuestRecords.Add(newGuest);
            
            var room = _context.Rooms.FirstOrDefault(r => r.RoomId == guest.RoomId);
            if (room == null)
            {
                throw new Exception("房间不存在");
            }
            room.RoomStateId = 1; // 设置房间状态为已入住
            _context.SaveChanges();
            return true; // 返回成功
        }

        /// <summary>
        /// 查询时间段内未付款顾客信息
        /// </summary>
        /// <returns></returns>
        public List<GuestRecordBusiness> GetAllGuest(DateTime Sdate, DateTime Edate)
        {
            var resideId = 1; // 替换为实际参数

            var query = _context.GuestRecords
                .Where(g =>
                    g.ResideId == resideId &&
                    g.ResideDate >= Sdate &&
                    g.ResideDate <= Edate
                )
                .Select(g => new GuestRecordBusiness()
                {
                    RoomId = g.RoomId,
                    GuestName = g.GuestName,
                    IdentityId = g.IdentityId,
                    ResideDate = g.ResideDate,
                    Deposit = g.Deposit,
                    //ResideName = g.ResideState.ResideName, // 导航属性
                    ResideId = g.ResideId,
                    DishPrice = g.DishPrice,
                    LeaveDate = g.LeaveDate
                });

            var results = query.ToList();
            return results;

            //List<GuestRecordBusiness> guest = new List<GuestRecordBusiness>();
            //StringBuilder sb = new StringBuilder();
            //string sql = "select g.RoomId, g.GuestName, g.IdentityId, g.ResideDate, g.Deposit, r.ResideName, g.ResideId, g.DishPrice, g.LeaveDate, g.roomId from GuestRecord as g " +
            //    "inner join ResideState as r on g.ResideId=r.ResideId " +
            //    "where g.ResideId=@resideId and ResideDate between @sdate and @edate";

            //SqlParameter[] para = { new SqlParameter("@resideId", 1),
            //                        new SqlParameter("sdate",Sdate),
            //                        new SqlParameter("edate",Edate)
            //                      };
            //SqlDataReader reader = SqlHelper.DataReader(sql, CommandType.Text, para);
            //while (reader.Read())
            //{
            //    GuestRecordBusiness g = new GuestRecordBusiness();
            //    g.GuestName = reader["GuestName"].ToString();
            //    g.IdentityId = reader["IdentityId"].ToString();
            //    g.ResideDate = Convert.ToDateTime(reader["ResideDate"]);
            //    g.Deposit = Convert.ToDecimal(reader["Deposit"]);
            //    g.RoomId = Convert.ToInt32(reader["RoomId"]);
            //    g.DishPrice = Convert.ToDecimal(reader["DishPrice"]);
            //    guest.Add(g);
            //}
            //reader.Close();
            //return guest;
        }

        /// <summary>
        /// 查询所有未付款顾客信息
        /// </summary>
        /// <returns></returns>
        public List<GuestRecordBusiness> GetAllGuest()
        {
            // todo: 使用LINQ查询未付款顾客信息
            var query = _context.GuestRecords
                        .Where(g => g.ResideId == 1)
                        .Select(g => new GuestRecordBusiness()
                        {
                            GuestId = g.GuestId,
                            GuestName = g.GuestName,
                            IdentityId = g.IdentityId,
                            ResideDate = g.ResideDate,
                            Deposit = g.Deposit,
                            DishPrice = g.DishPrice,
                            RoomId = g.RoomId
                        });

            var results = query.ToList();
            return results;

            //List<GuestRecordBusiness> guest = new List<GuestRecordBusiness>();
            //string sql = "select GuestId,GuestName,IdentityId,ResideDate,Deposit,DishPrice,RoomId from GuestRecord as g inner join ResideState as r on g.ResideId=r.ResideId where g.ResideId=@resideId";
            //SqlParameter[] para = { new SqlParameter("@resideId", 1) };
            //SqlDataReader reader = SqlHelper.DataReader(sql, CommandType.Text, para);
            //while (reader.Read())
            //{
            //    GuestRecordBusiness g = new GuestRecordBusiness();
            //    g.GuestId = Convert.ToInt32(reader["GuestId"]);
            //    g.GuestName = reader["GuestName"].ToString();
            //    g.IdentityId = reader["IdentityId"].ToString();
            //    g.ResideDate = Convert.ToDateTime(reader["ResideDate"]);
            //    g.Deposit = Convert.ToDecimal(reader["Deposit"]);
            //    g.DishPrice = Convert.ToDecimal(reader["DishPrice"]);
            //    g.RoomId = Convert.ToInt32(reader["RoomId"]);
            //    guest.Add(g);
            //}
            //reader.Close();
            //return guest;
        }

        /// <summary>
        /// 查询所有已付款客户信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<GuestRecord> GetCusInfo(string name)
        {
            var query = _context.GuestRecords
                        .Where(g => g.ResideId == 2)
                        .Select(g => new GuestRecord()    
                        {
                           RoomId = g.RoomId,
                            GuestName = g.GuestName,
                            Gender = g.Gender,
                            IdentityId = g.IdentityId,
                            Phone = g.Phone,
                            ResideDate = g.ResideDate,
                            LeaveDate = g.LeaveDate
                        });

            var results = query.ToList();
            return results;

            //StringBuilder sb = new StringBuilder();
            //sb.AppendLine("select RoomId,GuestName,Gender,IdentityId,Phone,ResideDate,LeaveDate from GuestRecord as g,ResideState as r where g.ResideId=r.ResideId and g.ResideId=2");
            //if (!string.IsNullOrEmpty(name))
            //{
            //    sb.AppendLine(" and g.GuestName like '%'+@guestName+'%'");
            //}
            //SqlParameter[] para = { new SqlParameter("@guestName", name) };
            //SqlDataReader reader = SqlHelper.DataReader(sb.ToString(), CommandType.Text, para);
            //List<GuestRecord> record = new List<GuestRecord>();
            //while (reader.Read())
            //{
            //    GuestRecord gr = new GuestRecord();
            //    gr.RoomId = Convert.ToInt32(reader["RoomId"]);
            //    gr.GuestName = reader["GuestName"].ToString();
            //    gr.Gender = Convert.ToInt32(reader["Gender"]) == 0 ? "男" : "女";
            //    gr.IdentityId = reader["IdentityId"].ToString();
            //    gr.Phone = reader["Phone"].ToString();
            //    gr.ResideDate = Convert.ToDateTime(reader["ResideDate"]);
            //    gr.LeaveDate = Convert.ToDateTime(reader["LeaveDate"]);
            //    record.Add(gr);
            //}
            //reader.Close();
            //return record;
        }

        /// <summary>
        /// 房间类型价格
        /// </summary>
        /// <param name="roomId"></param>
        /// <returns></returns>
        public decimal GetMoney(int roomId)
        {            
            var price = _context.Rooms
                        .Where(r => r.RoomId == roomId)
                        .Select(r => r.RoomType.TypePrice)
                        .FirstOrDefault();
            return price;

            //string sql = "select TypePrice from RoomType as r inner join Room as m on r.TypeId=m.RoomTypeId where m.RoomId=@roomId";
            //SqlParameter[] para = { new SqlParameter("@roomId", roomId) };
            //return (decimal)SqlHelper.ExecuteScalar(sql, CommandType.Text, para);
        }


        /// <summary>
        /// 更新结账状态,房间状态,交易号
        /// </summary>
        /// <param name="roomId"></param>
        /// <returns></returns>
        public bool UpdateState(GuestRecord gr)
        {            
            var guestRecord = _context.GuestRecords.FirstOrDefault(g => g.RoomId == gr.RoomId);
            if (guestRecord == null)
            {
                throw new Exception("未找到对应的入住记录");
            }

            guestRecord.ResideId = 2; // 设置为已结账状态
            guestRecord.TotalMoney = gr.TotalMoney;
            guestRecord.LeaveDate = gr.LeaveDate;
            guestRecord.TradeNo = gr.TradeNo;
            
            var room = _context.Rooms.FirstOrDefault(r => r.RoomId == gr.RoomId);
            if (room == null)
            {
                throw new Exception("未找到对应的房间");
            }
            room.RoomStateId = 2; // 设置房间状态为已结账
            _context.SaveChanges();
            return true;

            //string sql = @"update GuestRecord set ResideId=@resideId,TotalMoney=@totalMoney,LeaveDate=@leaveDate,TradeNo=@tradeNo where RoomId=@roomId;"
            //            +"update Room set RoomStateId=@roomStateId where RoomId=@roomId;";            
            //SqlParameter[] para = { new SqlParameter("@resideId",2),
            //                          new SqlParameter("@totalMoney",gr.TotalMoney),
            //                          new SqlParameter("leaveDate",gr.LeaveDate),
            //                          new SqlParameter("tradeNo",gr.TradeNo),
            //                          new SqlParameter("roomId",gr.RoomId),
            //                        new SqlParameter("roomStateId",2)
            //                      };
            //return SqlHelper.GetExecuteNonQuery(sql, CommandType.Text, para);
        }
    }
}
